package com.yuanshi.faceword

import org.apache.spark.sql.SparkSession

/**
  * 统计用户&内容运营excel表格中的 颜世界用户运营数据_小象 的相关指标
  */
object FaceWordUserOpration {

  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().enableHiveSupport().config("hive.exec.dynamic.partition", true)
      .config("hive.exec.dynamic.partition.mode", "nonstrict").appName("FaceWordUserOpration").getOrCreate()

    var dt: String = args(0)

    //统计小象app的全站日活,字段1(FaceWordContentOpration中已做好)

    //统计颜世界单日新增，字段3,4,8
    spark.sql(
      s"""
        |select
        |count(today_temporary_id) uesrnew_today_cnts7,
        |count(m_temporary_id) uesrnew_cnts，   -- 颜世界日新
        |count(m_temporary_id)/count(temporary_id)  -- 新用户占比
        |from
        |(
        |select
        |if(min(create_time) = '${dt}' and create_time = '${dt}',temporary_id,null) today_temporary_id,
        |if(min(create_time) = '${dt}', temporary_id,null) m_temporary_id,
        |temporary_id
        |from
        |face_value_circle_video_play_info
        |where ext2 = ''
        |group by temporary_id
        |) t1
      """.stripMargin)

    //统计首次登录颜世界在7天之内的用户当日活跃数，字段4

    //统计活跃用户相关指标，字段2,5,6
    spark.sql(
      s"""
        |select
        |count(distinct temporary_id) as fw_user_dayact,
        |count(distinct if(create_time >= date_sub('${dt}',7),temporary_id,null)) temporary_id7,
        |count(distinct if(create_time >= date_sub('${dt}',30),temporary_id,null) ) temporary_id30
        |from
        |face_value_circle_video_play_info
        |where ext2 = '' and dt = ${dt}
      """.stripMargin)

    //统计日活比（颜世界日活/小象日活），字段7

    //统计播放用户相关字段,字段9，10，11，12，13
    spark.sql(
      s"""
        |select
        |dayact_bf,
        |incre_bf,
        |incre_bf7,
        |incre_bf30,
        |dayact_bf/count(temporary_id) as bf_rate
        |from
        |(
        |select
        |if(log_type = 2 and create_time = '${dt}',temporary_id,null) dayact_bf,
        |if(log_type = 2 and min(create_time) = '${dt}',temporary_id,null) incre_bf,
        |if(log_type = 2 and min(create_time) = '${dt}',temporary_id,null) incre_bf7,
        |if(log_type = 2 and min(create_time) = '${dt}',temporary_id,null) incre_bf30,
        |temporary_id
        |from  v
        |face_value_circle_video_play_info
        |group by temporary_id
        |) t1
      """.stripMargin)

    //统计留存相关字段
    spark.sql(
      """
        |select
        |count(t2.temporary_id2) as stay2,
        |count(t2.temporary_id7) as stay7,
        |count(t2.temporary_id15) as stay15,
        |count(t2.temporary_id30) as stay30,
        |
        |count(t2.temporary_id_bf2) as bf_stay2,
        |count(t2.temporary_id_bf7) as bf_stay7,
        |count(t2.temporary_id_bf15) as bf_stay15,
        |count(t2.temporary_id_bf30) as bf_stay30,
        |from
        |(
        |select
        |temporary_id
        |from
        |face_value_circle_video_play_info
        |where create_time >= '2019-06-15' and create_time <= '2019-06-16'
        |) t1
        |join
        |(
        |select
        |case when create_time >= '2019-06-14' and create_time <= '2019-06-15' then temporary_id end temporary_id2,
        |case when create_time >= '2019-06-07' and create_time <= '2019-06-08' then temporary_id end temporary_id7,
        |case when create_time >= '2019-06-01' and create_time <= '2019-06-02' then temporary_id end temporary_id15,
        |case when create_time >= '2019-05-15' and create_time <= '2019-05-16' then temporary_id end temporary_id30,
        |
        |case when log_type = 2 and create_time >= '2019-06-14' and create_time <= '2019-06-15' then temporary_id end temporary_id_bf2,
        |case when log_type = 2 and create_time >= '2019-06-07' and create_time <= '2019-06-08' then temporary_id end temporary_id_bf7,
        |case when log_type = 2 and create_time >= '2019-06-01' and create_time <= '2019-06-02' then temporary_id end temporary_id_bf15,
        |case when log_type = 2 and create_time >= '2019-05-15' and create_time <= '2019-05-16' then temporary_id end temporary_id_bf30
        |
        |from
        |face_value_circle_video_play_info
        |) t2
        |on t1.temporary_id = t2.temporary_id2 or t1.temporary_id = t2.temporary_id7 or
        |t1.temporary_id = t2.temporary_id15 or t1.temporary_id = t2.temporary_id30
        |or t1.temporary_id = t2.temporary_id_bf2 or t1.temporary_id = t2.temporary_id_bf7
        |or t1.temporary_id = t2.temporary_id_bf15 or t1.temporary_id = t2.temporary_id_bf30
      """.stripMargin)

    spark.close()
  }
}
